/* *********************************************************************************
   Corregir las funciones que llaman los triggers de tabla satelites para recalcular
   art.descxlike.

   Ale - 14/12/2018                                                     
 ***********************************************************************************
 */
----------   funcion que llaman los triggers de tablas satelites al art para recalcular art.descxlike   ----------

CREATE OR REPLACE FUNCTION tg_refrescar_art_descxlike()
  RETURNS trigger AS
$BODY$
DECLARE
BEGIN
  IF (TG_OP = 'INSERT') OR (TG_OP = 'UPDATE') THEN
    UPDATE art SET descxlike = descxlike WHERE art.id = NEW.idart;
    RETURN NEW;
  ELSIF (TG_OP = 'DELETE') THEN
    UPDATE art SET descxlike = descxlike WHERE art.id = OLD.idart;
    RETURN OLD;
  END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE COST 100;
  
ALTER FUNCTION tg_refrescar_art_descxlike() OWNER TO abacouser;
 
----------------------    artr       ----------------------

CREATE OR REPLACE FUNCTION tg_artr_upd_artdescxlike()
  RETURNS trigger AS
$BODY$
BEGIN
  IF (NEW.Nombre <> OLD.Nombre) THEN
    UPDATE art SET descxlike = descxlike WHERE (art.tipoarta = NEW.tipoarta) AND (art.codartr = NEW.cod);    
  END IF;
  RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE COST 100;
  
ALTER FUNCTION tg_artr_upd_artdescxlike() OWNER TO abacouser;

------- agregar comentario "REGENERAR ..." a funcion ------

CREATE OR REPLACE FUNCTION tg_art_ins_upd_descxlike()
  RETURNS trigger AS
$BODY$

-- ==============================
-- NOTA PARA EL IMPLEMENTADOR:
-- La funcion original queda en las (Trigger Functions) del esquema public con el nombre: tg_art_ins_upd_descxlike_bak();
-- POR CUESTIONES DE ORDEN, NUNCA se debe modificar esta funcion, sino la homonima del esquema cliente.

-- REGENERAR descxlike completo (toda la tabla): UPDATE art SET descxlike = descxlike;
-- ==============================

DECLARE
vDescXLike VARCHAR;
BEGIN
    vDescXLike :=
	NEW.Cod || ' ' || NEW.nombre || ' ' || 
	(SELECT nombre FROM artr WHERE (artr.tipoarta = NEW.tipoarta) AND (artr.cod = NEW.codartr)) || ' ' ||
	COALESCE(NEW.ubicacion, '') || ' ' || COALESCE(NEW.infobrowser, '') || ' ' ||
	(SELECT array_to_string(ARRAY(SELECT cod FROM artcodbar WHERE idart = NEW.id), ' ')) || ' ' ||
	(SELECT array_to_string(ARRAY(SELECT artubi.cod FROM artubixart INNER JOIN artubi ON artubixart.idartubi = artubi.id WHERE artubixart.idart = NEW.id), ' '));

    vDescXLike := cliente.tg_ins_upd_descxlike(NEW.id, vDescXLike);

    vDescXLike := LOWER( SUBSTRING(vDescXLike, 1, 1000));

  -- postgresql evalua toda la condicion, da error el OLD si se usa en INSERT
   IF TG_OP = 'INSERT' THEN
        NEW.descxlike = vDescXLike;
   ELSIF vDescXLike <> COALESCE(OLD.descxlike, '') THEN
        NEW.descxlike = vDescXLike;
   END IF;
         
  RETURN NEW;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE  COST 100;

ALTER FUNCTION tg_art_ins_upd_descxlike() OWNER TO abacouser;


-- Actualizar art.descxlike

UPDATE art SET descxlike = descxlike;

-- =====================================
SELECT aaversion('400.03');
-- =====================================
